﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
namespace DAO
{
    public class DAOTongKetHocKy:DataProvider
    {
        public static DataTable DSThongTin(int hocky)
        {
            DataTable bang;
            OleDbConnection ketNoi = Connect();
            string chuoilenh = "select tenlop,siso,soluongdat, round(( soluongdat/siso )*100,1) as tyle from ( lop inner join (select count(*) as soluongdat,lop.malop  from lop inner join (select avg(diemtb) as diemcuoi,malop from bangdiemmonhoc where hocky=@hk group by hocky,malop,mshs) as a on a.malop = lop.malop where diemcuoi >=5 group by lop.malop ) as aa on aa.malop = lop.malop) inner join (select count(*) as siso,lop.malop from lop inner join hocsinh on hocsinh.malop = lop.malop group by lop.malop) as tt on tt.malop = lop.malop";
            OleDbCommand lenh = new OleDbCommand(chuoilenh, ketNoi);
            OleDbParameter values = new OleDbParameter("@hk", hocky);
            lenh.Parameters.Add(values);
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.SelectCommand = lenh;
            DataSet ds = new DataSet();
            da.Fill(ds);
            bang = ds.Tables[0];
            Disconnect();
            return bang;
        }
    }
}
